﻿using MySql.Data.MySqlClient;
using NRemedy;
using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wicresoft.UnifyShow.Tool.Model;
using Wicresoft.UnifyShow.Tool.Service;


namespace Wicresoft.UnifyShow.Tool.Init
{
    public class InitApp : Basic
    {
        public int Create(User_XModel model)
        {
            int status = 0;    //0-正常;1-已存在;2-异常报错

            #region  //UnifyShow
            status = CreateNewUser(model);
            #endregion

            #region  //自服务
            using (OracleConnection conSelf = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
            {
                conSelf.Open();
                try
                {
                    //查询用户信息是否已存在
                    string selectStr = "select count(*) from accountcopy where accountid = :accountid";
                    List<OracleParameter> cmdParms = new List<OracleParameter>();
                    cmdParms.Add(new OracleParameter("accountid", OracleDbType.NVarchar2, model.Name.ToLower(), ParameterDirection.Input));
                    OracleCommand cmd = new OracleCommand();
                    this.PrepareCommand(cmd, conSelf, null, CommandType.Text, selectStr, cmdParms);
                    int selectcount = Convert.ToInt32(cmd.ExecuteScalar());

                    if (selectcount > 0)
                    {
                        status = 1;
                    }
                    else
                    {
                        OracleCommand insertcmd = new OracleCommand();
                        this.PrepareCommand(insertcmd, conSelf, null, CommandType.Text, "insert into accountcopy (accountid, username, company, dept, position, email,phone) values ( :accountid, :username, :company, :department, :position, :email,:phone)",
                            new List<OracleParameter>()
                        {
                            new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                            new OracleParameter("username", OracleDbType.NVarchar2, 100, model.DisplayName, ParameterDirection.Input),
                            new OracleParameter("company", OracleDbType.NVarchar2, 100, model.Company, ParameterDirection.Input),
                            new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                            new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                            new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                            new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name.ToLower(),ParameterDirection.Input)
                        });
                        insertcmd.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    status = 2;
                    SysLog.WriteSysLog("Self \n" + e.ToString());
                }
            }
            #endregion

            #region  //ITSM
            if (status == 0)
            {
                try
                {
                    string ARServer = ConfigurationManager.AppSettings["ARServer"];
                    string ARUid = ConfigurationManager.AppSettings["ARUid"];
                    string ARPwd = ConfigurationManager.AppSettings["ARPwd"];
                    ARLoginContext context = new ARLoginContext(ARServer, ARUid, ARPwd);
                    ARProxy<CTMPeople> ar = new ARProxy<CTMPeople>(context);
                    var entryList = ar.GetEntryByQuery(string.Format("\'4\'=\"{0}\"", model.Name.ToLower()));
                    CTMPeople entry = null;
                    if (entryList.Count > 0)
                    {
                        status = 1;
                    }
                    else
                    {
                        entry = new CTMPeople
                        {
                            Assigned_To = model.Name.ToLower(),
                            //电话需确定
                            Company = model.Company,
                            //Site值需确定
                            //Site = "",需要AR存在这个Site才能添加
                            //组织没有填写部门
                            //Organization = employee.DEPT,  需要AR存在这个组织才能添加
                            Department = model.Department,
                            FirstName = model.DisplayName.Substring(1),
                            LastName = model.DisplayName.Substring(0, 1),
                            FullName = model.DisplayName,
                            InternetEmail = model.Email,
                            JobTitle = model.Position,
                            Short_Description = ".",
                            Submitter = "Demo",
                            PhoneNumberBusiness = "###",
                            ClientType = CTMPeople.ClientType_Enum.office,
                            z1DPassword = "User@123",
                            Organization = model.Company,                            //only 1 means enabled
                            Status = CTMPeople.Status_Enum.Enabled
                        };
                        ar.CreateEntry(entry);
                    }
                }
                catch (Exception e)
                {
                    status = 2;
                    SysLog.WriteSysLog("ITSM: \n" + e.ToString());
                }
            }
            #endregion

            #region  //MySQL
            if (status == 0)
            {
                using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString))
                {

                    try
                    {
                        conn.Open();
                        Dictionary<string, int> orgcodes = new Dictionary<string, int>()
                                {
                                    {"泉州市烟草专卖局", 10350501 },
                                    {"中国烟草福建进出口有限责任公司", 88880229      },
                                    {"福州市烟草专卖局", 10350101 },
                                    {"三明市烟草专卖局", 10350401 },
                                    {"龙岩市烟草专卖局", 10350801 },
                                    {"福建武夷烟叶有限公司", 88880227 },
                                    {"福建省烟草专卖局", 88880218  },
                                    {"漳州市烟草专卖局", 10350601 },
                                    {"南平市烟草专卖局", 10350701 },
                                    {"莆田市烟草专卖局", 10350301 },
                                    {"福建烟草海晟投资管理有限公司", 99990229   },
                                    {"宁德市烟草专卖局", 10350901 },
                                    {"厦门市烟草专卖局", 10350201 },
                                    {"福建三明金叶复烤有限公司", 88880228   }
                                };


                        MySqlCommand command = new MySqlCommand();
                        command.Connection = conn;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "create_person";//name of procdure
                        command.Parameters.Add(new MySqlParameter("personCN", model.Name.ToLower()));
                        command.Parameters.Add(new MySqlParameter("personCode", Guid.NewGuid().ToString()));
                        command.Parameters.Add(new MySqlParameter("personuid", model.Name.ToLower()));
                        command.Parameters.Add(new MySqlParameter("personmail", null));
                        command.Parameters.Add(new MySqlParameter("parentCode", orgcodes[model.Company]));
                        command.Parameters.Add(new MySqlParameter("personPwd", model.Password));//pwd maybe encrypted
                        MySqlParameter param = new MySqlParameter("@entryid", model.ID);
                        param.Direction = ParameterDirection.Output;
                        command.Parameters.Add(param);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        status = 2;
                        SysLog.WriteSysLog("MySQL: \n" + e.ToString());
                    }
                }
            }
            #endregion

            return status;
        }


        public int CreateNewUser(User_XModel model)
        {
            int status = 0;

            OracleConnection conn = null;
            OracleCommand cmd1 = null;
            OracleCommand insertcmd = null;
            try
            {
                conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString);
                //查询用户信息是否已存在
                string selectStr1 = "select ID from USER_X where NAME = :NAME";
                cmd1 = conn.CreateCommand();
                List<OracleParameter> cmdParms = new List<OracleParameter>();
                cmdParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, model.Name.ToLower(), ParameterDirection.Input));
                this.PrepareCommand(cmd1, conn, null, CommandType.Text, selectStr1, cmdParms);
                int userid = Convert.ToInt32(cmd1.ExecuteScalar());

                if (userid > 0)
                {
                    status = 1;
                }
                else
                {
                    string insertStr = "insert into USER_X (NAME, DISPLAYNAME, COMPANY, DEPARTMENT, POSITION,DISABLED,LASTMODIFIEDTIME) values ( :NAME, :DISPLAYNAME, :COMPANY, :DEPARTMENT, :POSITION,:DISABLED,:LASTMODIFIEDTIME)";
                    List<OracleParameter> insertParms = new List<OracleParameter>();
                    insertParms.Add(new OracleParameter("DISPLAYNAME", OracleDbType.NVarchar2, 100, (model.DisplayName == null ? "" : model.DisplayName), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("COMPANY", OracleDbType.NVarchar2, 100, (model.Company == null ? "" : model.Company), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("DEPARTMENT", OracleDbType.NVarchar2, 100, (model.Department == null ? "" : model.Department), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("POSITION", OracleDbType.NVarchar2, 100, (model.Position == null ? "" : model.Position), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, 100, model.Name.ToLower(), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("LASTMODIFIEDTIME", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("DISABLED", OracleDbType.Int32, 0, ParameterDirection.Input));

                    insertcmd = conn.CreateCommand();
                    this.PrepareCommand(insertcmd, conn, null, CommandType.Text, insertStr, insertParms);
                    insertcmd.ExecuteNonQuery();
                    userid = Convert.ToInt32(cmd1.ExecuteScalar());
                }

            }
            catch (Exception e)
            {
                status = 2;
                SysLog.WriteSysLog("UnifyShow: \n" + e.ToString());
            }
            finally
            {
                if (cmd1 != null)
                {
                    cmd1.Dispose();
                }
                if (insertcmd != null)
                {
                    insertcmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return status;
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }

    }
}
